//****************************************************************
//* Copyright (c) 2004 Ford Motor Company. All Rights Reserved.
//*
//* $$Workfile:   CatalogDao.java  $$
//* $$Revision:   1.1  $$
//* $$Author:   aponte  $$
//* $$Date:   Dec 12 2008 13:33:34  $$
//*
//*****************************************************************
package ar.com.ford.it.fullEconomics.persistence.dao;

import java.io.FileNotFoundException;
import java.sql.Timestamp;
import java.util.Collection;
import oracle.toplink.expressions.Expression;
import oracle.toplink.expressions.ExpressionBuilder;
import oracle.toplink.queryframework.ReadAllQuery;
import oracle.toplink.queryframework.ReportQuery;
import oracle.toplink.sessions.Session;
import oracle.toplink.tools.sessionmanagement.SessionManager;
import ar.com.ford.it.fullEconomics.persistence.dto.Ncas012VehicleVw;
import ar.com.ford.it.fullEconomics.persistence.dto.Ncas101CatalogVw;
import ar.com.ford.it.fullEconomics.persistence.dto.Teesp11Volume;
import com.ford.it.logging.ILogger;
import com.ford.it.logging.LogFactory;
import com.ford.it.persistence.PersistenceException;

public class CatalogDao extends Dao {

	private static final String CLASS_NAME = CatalogDao.class.getName();

	private static final ILogger log = LogFactory.getInstance().getLogger(
			CLASS_NAME);

	/**
	 * busco los catalogos que no tengan volumenes asociados
	 * 
	 * @return
	 * @throws PersistenceException
	 */
	public Collection<Ncas101CatalogVw> findCatalogWithoutVolume(Timestamp fechaIn, Timestamp fechaOut ) throws PersistenceException {

		String METHOD_NAME = "findCatalogWithoutVolume";
		log.entering(CLASS_NAME, METHOD_NAME);

		Collection<Ncas101CatalogVw> resultCol = null;

		Session session = null;

		try {
			session = SessionManager.getManager().getSession(
					this.getSessionName());
		} catch (FileNotFoundException e) {
			throw new PersistenceException(e);
		}
		ExpressionBuilder builder = new ExpressionBuilder();
		ReadAllQuery query = new ReadAllQuery(Ncas101CatalogVw.class);
		ExpressionBuilder inExpression = new ExpressionBuilder();
		
		Expression exp = null;
		
		exp = inExpression.get("ncas101PlntcatVw").get("ncas101CatalogVw").get("cas101CatalModelyearCodeC").equal(builder.get("cas101CatalModelyearCodeC")); // LSPARACI: corregido, decia ModelYear
		exp = exp.and(inExpression.get("ncas101PlntcatVw").get("ncas101CatalogVw").get("cas101CatalTmaCodeC").equal(builder.get("cas101CatalTmaCodeC"))); // LSPARACI: corregido, decia ModelYear
		exp = exp.and(inExpression.get("ncas101PlntcatVw").get("ncas101CatalogVw").get("cas101CatalSeqCodeC").equal(builder.get("cas101CatalSeqCodeC"))); // LSPARACI: corregido, decia ModelYear
// LSPARACI : add?		exp = exp.and(inExpression.get("ncas101PlntcatVw").get("ncas101CatalogVw").get("ncas101VehicleVw").get("cas101VehicleCodeC").equal(builder.getFieldValue("cas101VehicleCodeC")));
// LSPARACI : add?		exp = exp.and(inExpression.get("ncas101PlntcatVw").get("cas101PlantCodeC").get("eesg01PlantCodeC").equal(builder.getFieldValue("cas101PlantCodeC")));
		
		ReportQuery report = new ReportQuery(Teesp11Volume.class, exp);
		report.addAttribute("cas101CatalModelyearCodeC", inExpression.get(
				"ncas101PlntcatVw").get("ncas101CatalogVw").get(
				"cas101CatalModelyearCodeC"));
		report.addAttribute("cas101CatalTmaCodeC", inExpression.get(
				"ncas101PlntcatVw").get("ncas101CatalogVw").get(
				"cas101CatalTmaCodeC"));
		report.addAttribute("cas101CatalSeqCodeC", inExpression.get(
				"ncas101PlntcatVw").get("ncas101CatalogVw").get(
				"cas101CatalSeqCodeC"));
		
		Expression expFinal = builder.exists(report).not();		
		
		//comparo la vigencia del catalogo
		expFinal = expFinal.and(builder.get("cas101CatalInDateY").lessThan(fechaIn));
		expFinal = expFinal.and(builder.get("cas101CatalOutDateY").greaterThan(fechaOut));
				
		query.setSelectionCriteria(expFinal);

		resultCol = (Collection<Ncas101CatalogVw>) session.executeQuery(query);

		log.exiting(CLASS_NAME, METHOD_NAME);
		return resultCol;
	}
	
	/**
	 * busco los catalogos por vehiculo y fecha
	 * 
	 * @return
	 * @throws PersistenceException
	 */
	public Collection<Ncas101CatalogVw> findCatalogsByVehicleDate(Ncas012VehicleVw vehicleLine, Timestamp fechaIn, Timestamp fechaOut ) throws PersistenceException {

		String METHOD_NAME = "findCatalogsByVehicleDate";
		log.entering(CLASS_NAME, METHOD_NAME);

		Collection<Ncas101CatalogVw> resultCol = null;

		Session session = null;

		try {
			session = SessionManager.getManager().getSession(
					this.getSessionName());
		} catch (FileNotFoundException e) {
			throw new PersistenceException(e);
		}
		ExpressionBuilder builder = new ExpressionBuilder();
		ReadAllQuery query = new ReadAllQuery(Ncas101CatalogVw.class);
		
		Expression exp = null;
		
		//comparo la vigencia del catalogo
		exp = builder.get("cas101CatalInDateY").lessThan(fechaIn);
		exp = exp.and(builder.get("cas101CatalOutDateY").greaterThan(fechaOut));
		
		//comparo el vehiculo
		exp = exp.and(builder.get("cas101VehicleCodeC").equal(vehicleLine));
				
		query.setSelectionCriteria(exp);

		resultCol = (Collection<Ncas101CatalogVw>) session.executeQuery(query);

		log.exiting(CLASS_NAME, METHOD_NAME);
		return resultCol;
	}

}
